********************************************************************************
**	PURPOSE: Create transition matrices, create T2, RT2
**							
**	INPUTS: Wide with outcomes.dta
**	
**	OUTPUTS: Transition Matrix for Having a Credit Score - Table 2
**			 Transition Matrix of FICO scores_cbltakers.xlsx - Response Letter Table 2
**			 Transition Matrix of FICO scores_extratakers.xlsx - Response Letter Table 2
**			 Transition Matrix of FICO scores_cblnontakers.xlsx - Response Letter Table 2
**			 Transition Matrix of FICO scores_extranontakers.xlsx - Response Letter Table 2 
**
**	CREATED/EDITED BY: Nora Gregory, Kayla Wilding, Leah Kim, Hasan Ahamed
**
**  DATE LAST EDITED: 2/28/2023
********************************************************************************
/*******************************************************************************
0. Setup 
********************************************************************************/
clear all

use "$adta/Wide with outcomes.dta", clear 

*The same people are missing ltca and htca so doesn't matter which are in the !mi for all the sum statements below
assert mi(htca) if mi(ltca)
assert mi(ltca) if mi(htca)
assert !mi(htca) if !mi(ltca)
assert !mi(ltca) if !mi(htca)

*This variable will be the netchange in people who gained a score
gen netchange18 = .
replace netchange18 = 1 if (scoredf1 == 0  & scoredf18 == 1) 
replace netchange18 = 0 if (scoredf1 == scoredf18 & !mi(scoredf1) & !mi(scoredf18))
replace netchange18 = -1 if (scoredf1 == 1 & scoredf18 == 0)

/*******************************************************************************
1. Response Letter Table 2 
********************************************************************************/

gen ficobin18 = 1 if ficoscore0818 <= 589
replace ficobin18 = 2 if ficoscore0818 >= 590 & ficoscore0818 <= 619
replace ficobin18 = 3 if ficoscore0818 >= 620	& ~missing(ficoscore0818)

gen ficobin1 = 1 if (ficoscore081 <= 589)
replace ficobin1 = 2 if (ficoscore081 >= 590 & ficoscore081 <= 619)
replace ficobin1 = 3 if (ficoscore081 >= 620) & ~missing(ficoscore081)

table ficobin1 ficobin18 if flag_cblgroup == 1 & op18 == 1, stat(frequency) stat(proportion, across(ficobin18)) nformat("%5.4f" proportion) 
collect export  "$outputtables/Transition Matrix of FICO scores_cbltakers.xlsx", replace

table ficobin1 ficobin18 if flag_extragroup == 1 & op18 == 1, stat(frequency) stat(proportion, across(ficobin18)) nformat("%5.4f" proportion)
collect export  "$outputtables/Transition Matrix of FICO scores_extratakers.xlsx", replace

table ficobin1 ficobin18 if flag_cblgroup == 1 & op18 == 0, stat(frequency) stat(proportion, across(ficobin18)) nformat("%5.4f" proportion) 
collect export  "$outputtables/Transition Matrix of FICO scores_cblnontakers.xlsx", replace

table ficobin1 ficobin18 if flag_extragroup == 1 & op18 == 0, stat(frequency) stat(proportion, across(ficobin18)) nformat("%5.4f" proportion)
collect export  "$outputtables/Transition Matrix of FICO scores_extranontakers.xlsx", replace

/*******************************************************************************
2. Table 2 
********************************************************************************/

putexcel set "$outputtables/Transition Matrix of Having a Credit Score.xlsx", modify
	*full sample, treatment group, 
		sum scoredf18 if scoredf1 == 1 & flag_cblgroup == 1 & !mi(htca)
			putexcel E8 = (`r(N)'), font("Times New Roman", 10) hcenter nformat("0")
				sleep 800
			putexcel F8 = (`r(mean)'), font("Times New Roman", 10) hcenter nformat("0.#00")
				sleep 800
			putexcel G8 = (1 - `r(mean)'), font("Times New Roman", 10) hcenter nformat("0.#00")
				sleep 800 

		sum scoredf18 if scoredf1 == 0 & flag_cblgroup == 1 & !mi(htca)
			putexcel E9 = (`r(N)'), font("Times New Roman", 10) hcenter nformat("0")
				sleep 800
			putexcel F9 = (`r(mean)'), font("Times New Roman", 10) hcenter nformat("0.#00")
				sleep 800
			putexcel G9 = (1 - `r(mean)'), font("Times New Roman", 10) hcenter nformat("0.#00")
				sleep 800

		count if scoredf18 == 1 & flag_cblgroup == 1 & !mi(htca)
			putexcel F7 = (`r(N)'), font("Times New Roman", 10) hcenter nformat("0")
				sleep 800
		count if scoredf18 == 0 & flag_cblgroup == 1 & !mi(htca)
			putexcel G7 = (`r(N)'), font("Times New Roman", 10) hcenter nformat("0")
				sleep 800 			

		sum netchange18 if flag_cblgroup == 1 & !mi(htca)
			putexcel H10 = (`r(mean)'), font("Times New Roman", 10) hcenter nformat("0.#00")
				sleep 800

	*full sample control nogroup1
		sum scoredf18 if scoredf1 == 1 & flag_extragroup == 1 & !mi(htca)
			putexcel L8 = (`r(N)'), font("Times New Roman", 10) hcenter nformat("0")
				sleep 800
			putexcel M8 = (`r(mean)'), font("Times New Roman", 10) hcenter nformat("0.#00")
				sleep 800
			putexcel N8 = (1 - `r(mean)'), font("Times New Roman", 10) hcenter nformat("0.#00")
				sleep 800
			

		sum scoredf18 if scoredf1 == 0 & flag_extragroup == 1 & !mi(htca)
			putexcel L9 = (`r(N)'), font("Times New Roman", 10) hcenter nformat("0")
				sleep 800
			putexcel M9 = (`r(mean)'), font("Times New Roman", 10) hcenter nformat("0.#00")
				sleep 800
			putexcel N9 = (1 - `r(mean)'), font("Times New Roman", 10) hcenter nformat("0.#00")
				sleep 800

		count if scoredf18 == 1 & flag_extragroup == 1 & !mi(htca)		
			putexcel M7 = (`r(N)'), font("Times New Roman", 10) hcenter nformat("0")
				sleep 800
		count if scoredf18 == 0 & flag_extragroup == 1 & !mi(htca)
			putexcel N7 = (`r(N)'), font("Times New Roman", 10) hcenter nformat("0")
				sleep 800

		sum netchange18 if flag_extragroup == 1 & !mi(htca)
			putexcel O10 = (`r(mean)'), font("Times New Roman", 10) hcenter nformat("0.#00")
				sleep 800

	*ttest the net change 
		ttest netchange18 if !mi(htca), by(flag_extragroup)
			putexcel P10 = (`r(mu_1)' - `r(mu_2)'), font("Times New Roman", 10) hcenter nformat("0.#00")
				sleep 800
			putexcel P11 = (`r(p)'), font("Times New Roman", 10) hcenter nformat("0.#00")
				sleep 800

**EOF**

